CREATE DATABASE SmartFoodWaste4DB;
USE SmartFoodWaste4DB;





CREATE TABLE Foodbanks (
    FoodbanksID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    town VARCHAR(50),
    ContactEmail VARCHAR(100),
    phoneNumber VARCHAR(20)
);




CREATE TABLE Managers (
    ManagersID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    address VARCHAR(50),
    ContactEmail VARCHAR(100),
    phoneNumber VARCHAR(20)
);







CREATE TABLE Doners (
    DonersID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    items VARCHAR(50),
    quantity INT,
    phoneNumber VARCHAR(20),
    datedonated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FoodbanksID INT,
    FOREIGN KEY (FoodbanksID) REFERENCES Foodbanks(FoodbanksID)
);






CREATE TABLE Recipients (
    RecipientsID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    town VARCHAR(50),
    ContactEmail VARCHAR(100),
    phoneNumber VARCHAR(20),
    FoodbanksID INT,
    FOREIGN KEY (FoodbanksID) REFERENCES Foodbanks(FoodbanksID)
);





CREATE TABLE Employees (
    EmployeesID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    address VARCHAR(50),
    ContactEmail VARCHAR(100),
    phoneNumber VARCHAR(20),
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES Managers(ManagersID)
);






CREATE TABLE Distributions (
    DistributionID INT PRIMARY KEY AUTO_INCREMENT,
    DonerID INT,
    RecipientID INT,
    FoodbanksID INT,
    EmployeeID INT,
    quantityDistributed INT,
    dateDistributed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (DonerID) REFERENCES Doners(DonersID),
    FOREIGN KEY (RecipientID) REFERENCES Recipients(RecipientsID),
    FOREIGN KEY (FoodbanksID) REFERENCES Foodbanks(FoodbanksID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeesID)
);








INSERT INTO Foodbanks (Name, town, ContactEmail, phoneNumber) VALUES
  ('Davion Staff of Life Ltd', 'Hackney', 'hackney@example.com', '07964815810'),
  ('Buba Lookforward Ltd', 'Camden', 'camden@example.com', '07968815119'),
  ('Hafiz Food Chain Ltd', 'Croydon', 'croydon@example.com', '07968715119'),
  ('Ghazala Look After the Pure Ltd', 'Thornton Heath', 'thorntonheath@example.co.uk', '07964815112'),
  ('Tony Food Alliance Ltd', 'Islington', 'islington@example.co.uk', '07964815116');









INSERT INTO Managers (Name, address, ContactEmail, phoneNumber) VALUES
  ('Tamer Vigille', 'Hackney', 'tamer@example.com', '07964815303'),
  ('Alex Notice', 'Camden', 'alex@example.com', '07930315119'),
  ('Akeem Cooper', 'Thornton Heath', 'akeem@example.co.uk', '07964830412'),
  ('Benjamin Farrestor', 'Croydon', 'benjamin@example.co.uk', '07778830412'),
  ('Jodain Gilburt', 'Islington', 'jodain@example.co.uk', '07990715116');










INSERT INTO Doners (Name, items, quantity, phoneNumber, datedonated, FoodbanksID) VALUES
  ('Davion Farmers Ltd', 'Vegetable', 30, '07911111111', '2025-10-21', 1),
  ('Buba Food Market Ltd', 'Fruit Baskets', 50, '07922222222', '2025-10-21', 2),
  ('Ghazala Grocery Ltd', 'Fresh Bread', 30, '07933333333', '2025-10-21', 3),
  ('Hafiz Grocery Ltd', 'Fresh Bread, Chicken & Fruits', 30, '07944444444', '2025-10-21', 4),
  ('Tony Farming Ltd', 'Ground Food', 30, '07955555555', '2025-10-21', 5);










INSERT INTO Recipients (Name, town, ContactEmail, phoneNumber, FoodbanksID) VALUES
  ('James Author', 'Hackney', 'james@example.com', '07964915110', 1),
  ('Amoya Housen', 'Camden', 'amoya@example.com', '07964615119', 2),
  ('Jahmoy Maxwell', 'Thornton Heath', 'jahmoy@example.co.uk', '07564815112', 3),
  ('Alisa Howell', 'Croydon', 'alisa@example.co.uk', '07564815812', 4),
  ('Odaine Brown', 'Islington', 'odaine@example.co.uk', '07536481516', 5);











INSERT INTO Employees (Name, address, ContactEmail, phoneNumber, ManagerID) VALUES
  ('Miles Gowie', 'Hackney', 'miles@example.com', '07964815118', 1),
  ('Jerome Hamilton', 'Camden', 'jerome@example.com', '07967715119', 2),
  ('Shakira Elist', 'Thornton Heath', 'shakira@example.co.uk', '07064815112', 3),
  ('Kareem Kilic', 'Croydon', 'kareem@example.co.uk', '07069915112', 4),
  ('Oniel Roberts', 'Islington', 'oniel@example.co.uk', '07965815116', 5);










INSERT INTO Distributions (DonerID, RecipientID, FoodbanksID, EmployeeID, quantityDistributed, dateDistributed) VALUES
  (1, 1, 1, 1, 20, '2025-10-22'),
  (2, 2, 2, 2, 30, '2025-10-22'),
  (3, 3, 3, 3, 15, '2025-10-22'),
  (4, 4, 4, 4, 25, '2025-10-22'),
  (5, 5, 5, 5, 30, '2025-10-22');









CREATE VIEW DonerDistributionView AS
SELECT 
    d.Name AS DonerName,
    d.items,
    d.quantity AS DonatedQuantity,
    r.Name AS RecipientName,
    fb.Name AS Foodbank,
    e.Name AS DeliveredBy,
    dist.quantityDistributed,
    dist.dateDistributed
FROM Distributions dist
JOIN Doners d ON dist.DonerID = d.DonersID
JOIN Recipients r ON dist.RecipientID = r.RecipientsID
JOIN Foodbanks fb ON dist.FoodbanksID = fb.FoodbanksID
JOIN Employees e ON dist.EmployeeID = e.EmployeesID;







SELECT * FROM Foodbanks;
SELECT * FROM Doners;
SELECT * FROM Recipients;
SELECT * FROM Employees;
SELECT * FROM Managers;
SELECT * FROM Distributions;


SELECT * FROM DonerDistributionView;